package com.zjuh.goodboy.store.expr;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.zjuh.goodboy.misc.BaseQuery;
import com.zjuh.goodboy.parsers.ItemExpr;
import com.zjuh.goodboy.store.jdbc.BaseJdbcDaoSupport;

public class JdbcItemExprManager extends BaseJdbcDaoSupport implements ItemExprManager {

	@Override
	public void create(final ItemExpr itemExpr) {
		String sql = "insert into goodboy_item_expr (site_name, item_expr, url_expr, img_url_expr, hostname, input_url_expr, gmt_create, gmt_modified) values (?,?,?,?,?,?,now(), now())";
		this.getJdbcTemplate().update(sql, new Object[] {itemExpr.getSiteName(), itemExpr.getItemExpr(), 
					itemExpr.getUrlExpr(), itemExpr.getImgUrlExpr(), itemExpr.getHostname(), itemExpr.getInputUrlExpr()});
	}

	@Override
	public void delete(int id) {
		String sql = "delete from goodboy_item_expr where id = ?";
		this.getJdbcTemplate().update(sql, new Object[] {id});
	}
	
	private static ItemExpr row2ItemExpr(ResultSet rs) throws SQLException {
		ItemExpr itemExpr = new ItemExpr();
		itemExpr.setId(rs.getInt("id"));
		itemExpr.setImgUrlExpr(rs.getString("img_url_expr"));
		itemExpr.setItemExpr(rs.getString("item_expr"));
		itemExpr.setSiteName(rs.getString("site_name"));
		itemExpr.setUrlExpr(rs.getString("url_expr"));
		itemExpr.setInputUrlExpr(rs.getString("input_url_expr"));
		itemExpr.setHostname(rs.getString("hostname"));
		return itemExpr;
	}
	
	private static final String fullColumns = "id, site_name, item_expr, url_expr, img_url_expr, input_url_expr, hostname";

	@SuppressWarnings("unchecked")
	@Override
	public List<ItemExpr> queryForPage(BaseQuery baseQuery) {
		String sql = "select "+ fullColumns +" from goodboy_item_expr order by gmt_modified desc limit ?, ?";
		return (List<ItemExpr>)this.getJdbcTemplate().query(sql, new Object[] {baseQuery.getStartRow(), baseQuery.getPageSize()}, new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return row2ItemExpr(rs);
			}
			
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ItemExpr> queryByHostname(String hostname) {
		String sql = "select "+ fullColumns +" from goodboy_item_expr where hostname = ?";
		return (List<ItemExpr>)this.getJdbcTemplate().query(sql, new Object[] { hostname }, new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return row2ItemExpr(rs);
			}
		});
	}

	@Override
	public ItemExpr queryById(int id) {
		String sql = "select "+ fullColumns +" from goodboy_item_expr where id = ?";
		
		return (ItemExpr)this.getJdbcTemplate().queryForObject(sql, new Object[] { id }, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return row2ItemExpr(rs);
			}

		});
	}

	@Override
	public void update(ItemExpr itemExpr) {
		String sql = "update goodboy_item_expr set site_name = ? , item_expr = ?, url_expr =?, img_url_expr = ? , hostname = ?, input_url_expr = ?, gmt_modified = now() where id = ?";
		this.getJdbcTemplate().update(sql, 
				new Object[] {itemExpr.getSiteName(), itemExpr.getItemExpr(), itemExpr.getUrlExpr(), 
				itemExpr.getImgUrlExpr(), itemExpr.getHostname(), itemExpr.getInputUrlExpr(), itemExpr.getId()});
	}


}
